package cn.kys.modules.monitor.repository;

import cn.kys.modules.monitor.domain.Visits;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**功能描述：数据统计持久层
 * @Auther:http://www.kys.cn
 * @Date:2020/4/1
 * @Description:cn.kys.modules.monitor.repository
 * @version:1.0
 */
@Repository
public interface VisitsRepository extends JpaRepository<Visits,Long> {

    /**
     * findByDate
     * @param date 日期
     * @return Visits
     */
    Visits findByDate(String date);

    /**
     * 获得一个时间段的记录
     * @param date1 日期1
     * @param date2 日期2
     * @return List
     */
    @Query(value = "select * FROM visits where create_time between ?1 and ?2",nativeQuery = true)
    List<Visits> findAllVisits(java.sql.Timestamp date1, java.sql.Timestamp date2);


    /**
     * 根据省份统计近30天流量
     */
    //@Query(value = "select province as \"name\",count(province) as \"value\" FROM log where province IS NOT NULL and DATE_SUB(CURDATE(), INTERVAL 30 DAY) < date(create_time) GROUP BY province",nativeQuery = true)

   @Query(value = "SELECT province as name,COUNT(province) as value ,CURRENT_DATE FROM log where province IS NOT NULL and (CURRENT_DATE - INTERVAL '30' DAY) < date(create_time) GROUP BY province",nativeQuery = true)
   List<Map> findVisitsMonthDataByProvince();
}
